﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace BO
{
    /// <summary>
    /// Summary description for CategoryBO
    /// </summary>
    public class CategoryBO : DBConnectionBO
    {
        public CategoryBO()
            : base()
        {
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public List<Category> GetListCategory()
        {
            var listCategory = new List<Category>();
            const string query = "SELECT TOP 100 * FROM [PrivateBlog].[dbo].[Categories] WHERE Status = 1";
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            this.CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var category = new Category();

                category.Id = (string) dataTable.Rows[i][0];
                category.Name = (string) dataTable.Rows[i][1];
                category.LastDateUpdated = (DateTime) dataTable.Rows[i][2];
                category.LastUpdatedBy = (string) dataTable.Rows[i][3];
                category.Status = Convert.ToInt32(dataTable.Rows[i][4]);
                    
                listCategory.Add(category);
            }
            return listCategory;
        }

        /// <summary>
        /// Add new a category
        /// </summary>
        /// <param name="category"></param>
        /// <returns></returns>
        public int AddCategory(Category category)
        {
            var query = "INSERT INTO [PrivateBlog].[dbo].[Categories]([Id],[Name],[LastDateUpdated],[LastUpdatedBy],[Status]) VALUES"
                        + " ( '" +
                        category.Id + "', N'" +
                        category.Name + "', " +
                        "GETDATE(), '" +
                        category.LastUpdatedBy + "', " +
                        category.Status +
                        " ) ";
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Edit a category
        /// </summary>
        /// <param name="category"></param>
        /// <returns></returns>
        public int EditCategory(Category category)
        {
            var query = "UPDATE [PrivateBlog].[dbo].[Categories]" +
                        " SET Name = N'" + category.Name + "'" +
                        ",LastDateUpdated = GETDATE()" +
                        ",LastUpdatedBy = '" + category.LastUpdatedBy +
                        ",Status = " + category.Status +
                        " WHERE Id = " + category.Id;
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        public int DeleteCategory(Category category)
        {
            var query = "DELETE FROM [PrivateBlog].[dbo].[Categories] " +
                        " WHERE Id = " + category.Id;
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        public List<Category> GetInfoOfCategory(string idCategory)
        {
            var listCategory = new List<Category>();
            var query = "SELECT TOP 1 * FROM [PrivateBlog].[dbo].[Categories] WHERE Id like '" + idCategory + "' and Status = 1";
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            this.CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var category = new Category();

                category.Id = (string)dataTable.Rows[i][0];
                category.Name = (string)dataTable.Rows[i][1];
                category.LastDateUpdated = (DateTime)dataTable.Rows[i][2];
                category.LastUpdatedBy = (string)dataTable.Rows[i][3];
                category.Status = Convert.ToInt32(dataTable.Rows[i][4]);

                listCategory.Add(category);
            }
            return listCategory;
        }

    }
}